﻿using Entity;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace ServiceCore.Tool.NPOI
{
    /// <summary>
    ///  定义表头信息model
    /// </summary>
    public class Table_Head
    {
        public string label { get; set; }

        public string prop { get; set; }
    }
    public interface INpoi_service
    {
        void StartClearExcel();
        void ExcelExportService<T>(string title, List<T> list, string SheetName = "Sheet1", int row_height = 30 * 10, Action<Stream> dosomething = null);
        void ExcelExport_Template(List<string> head_content, string SheetName = "Sheet1", int row_height = 30 * 10, Action<Stream> dosomething = null);
        /// <summary>
        ///  指定列导出
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="title"></param>
        /// <param name="HeadList"></param>
        /// <param name="list"></param>
        /// <param name="SheetName"></param>
        /// <param name="row_height"></param>
        /// <param name="dosomething"></param>
        void ExcelExportService_Specify<T>(string title, List<Table_Head> HeadList, List<T> list, string SheetName = "Sheet1", int row_height = 30 * 10, Action<Stream> dosomething = null);
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="file">文件</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        DataTable ReadExcelToDataTable(Stream file, string sheetName = "Sheet1", bool isFirstRowColumn = true);
    } 
    public class Npoi_service : INpoi_service
    {
        private static Queue<AutoClear> startClear = new Queue<AutoClear>();
        private string path_info = "C://TempExcel/";
        public class AutoClear
        {
            public string path { get; set; }

            public DateTime Cleartime { get; set; }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="SheetName"></param>
        /// <param name="row_height"></param>
        /// <returns></returns>
        public void ExcelExportService<T>(string title, List<T> list, string SheetName = "Sheet1", int row_height = 30 * 10, Action<Stream> dosomething = null)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(SheetName);
            int title_colunt = 0;
            //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, typeof(T).GetProperties())); 
            sheet.DefaultRowHeight = 30 * 20;
            sheet.DefaultColumnWidth = 20;
            #region 设置标题
            ICellStyle tital_style = workbook.CreateCellStyle();
            tital_style.VerticalAlignment = VerticalAlignment.Center;
            tital_style.Alignment = HorizontalAlignment.Center;
            IFont tital_font = workbook.CreateFont();
            tital_font.Boldweight = (short)FontBoldWeight.Bold;
            tital_font.FontHeight = 18;
            tital_style.SetFont(tital_font);
            IRow title_heard = sheet.CreateRow(0);
            var tital_cell = title_heard.CreateCell(0);
            tital_cell.SetCellValue(title);
            tital_cell.CellStyle = tital_style;

            #endregion
            #region 设置表头 
            ICellStyle head_style = workbook.CreateCellStyle();
            head_style.VerticalAlignment = VerticalAlignment.Center;
            head_style.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            head_style.SetFont(font);
            IRow row_heard = sheet.CreateRow(1);
            row_heard.RowStyle = head_style;
            row_heard.Height = Convert.ToInt16(row_height);
            int cell_head_num = 0;
            foreach (var T_tyoe in typeof(T).GetProperties())
            {
                object[] objs = T_tyoe.GetCustomAttributes(typeof(DescriptionAttribute), true);
                if (objs.Length == 1)
                {
                    var obj = (DescriptionAttribute)objs[0];
                    if (obj.Description != "disable")
                    {
                        var cell = row_heard.CreateCell(cell_head_num);
                        cell.SetCellValue(obj.Description);
                        cell.CellStyle = head_style;
                        title_colunt++;
                    }
                    else
                    {
                        if (cell_head_num > 0)
                        {
                            cell_head_num--;
                        }
                        else
                        {
                            continue;
                        }
                    }
                }
                else
                {
                    var cell = row_heard.CreateCell(cell_head_num);
                    cell.SetCellValue(T_tyoe.Name);
                    cell.CellStyle = head_style;
                    title_colunt++;
                }
                //sheet.AutoSizeColumn(cell_head_num);
                cell_head_num++;
            }
            #endregion
            if (title_colunt > 0)
            {
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, title_colunt - 1));
            }


            var rowIndex = 2;
            foreach (var item in list)
            {
                IRow row = sheet.CreateRow(rowIndex);
                row.Height = Convert.ToInt16(row_height);
                int cell_num = 0;
                foreach (var cell in item.GetType().GetProperties())
                {
                    object[] objs = cell.GetCustomAttributes(typeof(DescriptionAttribute), true);
                    if (objs.Length == 1)
                    {
                        var obj = (DescriptionAttribute)objs[0];
                        if (obj.Description != "disable")
                        {
                            var name = cell.Name;
                            var value = cell.GetValue(item, null);
                            var type = value?.GetType() ?? typeof(object);
                            row.CreateCell(cell_num).SetCellValue(value?.ToString() ?? "");
                            //sheet.AutoSizeColumn(cell_num);
                            cell_num++;
                        }
                    }
                }
                rowIndex++;
            }
            string temp_id = Guid.NewGuid().ToString("N");
            if (!Directory.Exists(path_info))
            {
                Directory.CreateDirectory(path_info);
            }
            DateTime cleardt = DateTime.Now.AddMinutes(3);
            string temppath = path_info + "_" + temp_id + ".xlsx";
            using (var fs = new FileStream(temppath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
                //同时加入一个队列
                startClear.Enqueue(new AutoClear() { Cleartime = cleardt, path = temppath });
                //读取文件
                using (var newfile = new FileStream(temppath, FileMode.Open))
                {
                    dosomething?.Invoke(newfile);
                }
            }
        }


        /// <summary>
        /// 指令列名的数据导出
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="SheetName"></param>
        /// <param name="row_height"></param>
        /// <returns></returns>
        public void ExcelExportService_Specify<T>(string title, List<Table_Head> HeadList, List<T> list, string SheetName = "Sheet1", int row_height = 30 * 10, Action<Stream> dosomething = null)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(SheetName);
            int title_colunt = 0;
            //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, typeof(T).GetProperties())); 
            sheet.DefaultRowHeight = 30 * 20;
            #region 设置标题
            ICellStyle tital_style = workbook.CreateCellStyle();
            tital_style.VerticalAlignment = VerticalAlignment.Center;
            tital_style.Alignment = HorizontalAlignment.Center;
            IFont tital_font = workbook.CreateFont();
            tital_font.Boldweight = (short)FontBoldWeight.Bold;
            tital_font.FontHeight = 18;
            tital_style.SetFont(tital_font);
            IRow title_heard = sheet.CreateRow(0);
            var tital_cell = title_heard.CreateCell(0);
            tital_cell.SetCellValue(title);
            tital_cell.CellStyle = tital_style;
            #endregion
            #region 设置表头 
            ICellStyle head_style = workbook.CreateCellStyle();
            head_style.VerticalAlignment = VerticalAlignment.Center;
            head_style.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            head_style.SetFont(font);
            IRow row_heard = sheet.CreateRow(1);
            row_heard.RowStyle = head_style;
            row_heard.Height = Convert.ToInt16(row_height);
            int cell_head_num = 0;
            foreach (var head_filed in HeadList)
            {
                var cell = row_heard.CreateCell(cell_head_num);
                cell.SetCellValue(head_filed.label);
                cell.CellStyle = head_style;
                title_colunt++;
                sheet.AutoSizeColumn(cell_head_num);
                cell_head_num++;
            }
            #endregion
            if (title_colunt > 0)
            {
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, title_colunt - 1));
            }
            var rowIndex = 2;
            foreach (var item in list)
            {
                IRow row = sheet.CreateRow(rowIndex);
                row.Height = Convert.ToInt16(row_height);
                int cell_num = 0;
                foreach (var head_filed in HeadList)
                {
                    foreach (var Properties in item.GetType().GetProperties())
                    {
                        if (Properties.Name == head_filed.prop)
                        {
                            string value = Properties.GetValue(item, null) == null ? "" : Properties.GetValue(item, null).ToString();
                            if (value.ToLower() == "true")
                            {
                                value = "是";
                            }
                            else if (value.ToLower() == "false")
                            {
                                value = "否";
                            }
                            var type = value?.GetType() ?? typeof(object);
                            row.CreateCell(cell_num).SetCellValue(value?.ToString() ?? "");
                            //sheet.AutoSizeColumn(cell_num);
                            cell_num++;
                        }
                    }
                }
                rowIndex++;
            }
            string temp_id = Guid.NewGuid().ToString("N");
            if (!Directory.Exists(path_info))
            {
                Directory.CreateDirectory(path_info);
            }
            DateTime cleardt = DateTime.Now.AddMinutes(3);
            string temppath = path_info + "_" + temp_id + ".xlsx";
            using (var fs = new FileStream(temppath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
                //同时加入一个队列
                startClear.Enqueue(new AutoClear() { Cleartime = cleardt, path = temppath });
                //读取文件
                using (var newfile = new FileStream(temppath, FileMode.Open))
                {
                    dosomething?.Invoke(newfile);
                }
            }
        }

        /// <summary>
        /// 开始清理excel
        /// </summary>
        public void StartClearExcel()
        {
            Task.Factory.StartNew(() =>
            {
                try
                {
                    if (!Directory.Exists(path_info))
                    {
                        Directory.CreateDirectory(path_info);
                    }
                    //初始化需要删除的文件列表
                    foreach (var item in Directory.GetFiles(path_info))
                    {
                        try
                        {
                            DateTime cleardt = File.GetCreationTime(item).AddMinutes(3);
                            startClear.Enqueue(new AutoClear() { Cleartime = cleardt, path = item });
                        }
                        catch
                        {
                            continue;
                        }
                    }
                }
                catch
                {

                }
                //读取
                while (true)
                {
                    try
                    {
                        if (startClear.Count > 0)
                        {
                            var ClearExcel = startClear.Dequeue();
                            if (ClearExcel.Cleartime >= DateTime.Now)
                            {
                                //设置的清理时间还没到,重新入队
                                startClear.Enqueue(ClearExcel);
                            }
                            else
                            {
                                //删除指定文件
                                File.Delete(ClearExcel.path);
                            }
                        }
                    }
                    catch { }
                    finally
                    {
                        Thread.Sleep(5000);
                    }
                }
            });
        }

        /// <summary>
        ///  根据指定字段名 创建一个模板
        /// </summary>
        /// <param name="head_content"></param>
        /// <param name="SheetName"></param>
        /// <param name="row_height"></param>
        /// <param name="dosomething"></param>
        public void ExcelExport_Template(List<string> head_content, string SheetName = "Sheet1", int row_height = 30 * 10, Action<Stream> dosomething = null)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(SheetName);
            //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, typeof(T).GetProperties())); 
            sheet.DefaultRowHeight = 30 * 20;
            #region 设置表头 
            ICellStyle head_style = workbook.CreateCellStyle();
            head_style.VerticalAlignment = VerticalAlignment.Center;
            head_style.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            head_style.SetFont(font);
            IRow row_heard = sheet.CreateRow(0);
            row_heard.RowStyle = head_style;
            row_heard.Height = Convert.ToInt16(row_height);
            int cell_head_num = 0;
            foreach (var T_tyoe in head_content)
            {
                var cell = row_heard.CreateCell(cell_head_num);
                cell.SetCellValue(T_tyoe);
                cell.CellStyle = head_style;
                sheet.AutoSizeColumn(cell_head_num);
                cell_head_num++;
            }
            #endregion  
            string temp_id = Guid.NewGuid().ToString("N");
            if (!Directory.Exists(path_info))
            {
                Directory.CreateDirectory(path_info);
            }
            DateTime cleardt = DateTime.Now.AddMinutes(3);
            string temppath = path_info + "_" + temp_id + ".xlsx";
            using (var fs = new FileStream(temppath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
                //同时加入一个队列
                startClear.Enqueue(new AutoClear() { Cleartime = cleardt, path = temppath });
                //读取文件
                using (var newfile = new FileStream(temppath, FileMode.Open))
                {
                    dosomething?.Invoke(newfile);
                }
            }
        }
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="fileName">文件完整路径名</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        public DataTable ReadExcelToDataTable(Stream file, string sheetName = null, bool isFirstRowColumn = true)
        {
            //定义要返回的datatable对象
            DataTable data = new DataTable();
            //excel工作表
            ISheet sheet = null;
            //数据开始行(排除标题行)
            int startRow = 0;
            try
            {
                //根据文件流创建excel数据结构
                IWorkbook workbook = WorkbookFactory.Create(file);
                //IWorkbook workbook = new HSSFWorkbook(fs);
                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    //如果没有指定的sheetName，则尝试获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果第一行是标题列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
